package xipujewel.xipujewel.service;

import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.hibernate.Session;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import xipujewel.xipujewel.entity.app.ExchangeRate;
import xipujewel.xipujewel.entity.app.Jewel;
import xipujewel.xipujewel.repository.jpa.JewelDao;
import xipujewel.xipujewel.service.common.JpaManager;
import xipujewel.xipujewel.utils.jxlUtil;

/**
 * 
 * @author hang
 *
 */
@Component
@Transactional
public class JewelManager extends JpaManager<Jewel, JewelDao> {

	private static Logger logger = LoggerFactory.getLogger(JewelManager.class);

	private final static String[] orderJewelTitle1 = { "货号", "形状", "重量", "颜色", "净度", "切工", "抛光", "对称", "荧光", "证书",
			"尺寸", "国际报价", "公司", "折扣", "人民币价格", "库存", "证书号", "有无奶油色", "有无咖啡色", "有无绿色" };

	@Autowired
	private JewelDao jewelDao;

	public List<Jewel> getNotIsBuy() {
		return jewelDao.getNotIsBuy();
	}

	@Transactional(readOnly = false)
	public void deleteAll() {
		Session session = (Session) entityManager.getDelegate();
		String deleteOrderJewel = "delete from order_jewel where jewel_id in (select id from xipu_jewel where is_buy = 0)";
		session.createSQLQuery(deleteOrderJewel).executeUpdate();
		String deleteShopCart = "delete from xipu_shopping_cart where jewel_id in (select id from xipu_jewel where is_buy = 0)";
		session.createSQLQuery(deleteShopCart).executeUpdate();
		String deleteJewel = "delete from Jewel where isBuy=0";
		session.createQuery(deleteJewel).executeUpdate();
	}

	@Transactional(readOnly = false)
	public void delete(Long id) {
		Session session = (Session) entityManager.getDelegate();
		//		String deleteOrderJewel = "delete from order_jewel oj where oj.jewel_id=" + id;
		//		session.createSQLQuery(deleteOrderJewel).executeUpdate();
		String deleteShopCart = "delete from ShoppingCart sc where sc.jewel.id=" + id;
		session.createQuery(deleteShopCart).executeUpdate();
		jewelDao.delete(id);
	}

	@Transactional(readOnly = false)
	public void deleteByCompany(String company) {
		/*Session session = (Session) entityManager.getDelegate();
		String getShopCarByCompany = "From ShoppingCart sc where sc.jewel.company = " + company;
		List<ShoppingCart> shoppingCarts = session.createQuery(getShopCarByCompany).list();
		if (shoppingCarts != null && shoppingCarts.size() != 0) {
			for (ShoppingCart shoppingCart : shoppingCarts) {
				shoppingCartDao.delete(shoppingCart.getId());
			}company
		}
		String deleteJewel = "delete from xipu_jewel  where company=" + company + " and  is_buy = 0";
		session.createSQLQuery(deleteJewel).executeUpdate();*/
		Session session = (Session) entityManager.getDelegate();
		String deleteOrderJewel = "delete from order_jewel where jewel_id in (select id from xipu_jewel where is_buy = 0 and company="
				+ company + ")";
		session.createSQLQuery(deleteOrderJewel).executeUpdate();
		String deleteShopCart = "delete from xipu_shopping_cart where jewel_id in (select id from xipu_jewel where is_buy = 0 and company="
				+ company + ")";
		session.createSQLQuery(deleteShopCart).executeUpdate();
		String deleteJewel = "delete from Jewel where isBuy=0 and company=" + company;
		session.createQuery(deleteJewel).executeUpdate();
	}

	@Transactional(readOnly = false)
	public boolean upload(InputStream inputStream) {//上传excel表格
		try {
			List<Map<Integer, String>> table = jxlUtil.loadCXL(inputStream);
			int size = table.size();
			for (int i = 1; i < size; i++) {
				Jewel jewel = new Jewel();
				Map<Integer, String> r = table.get(i);

				int company = 0;
				String companyStr = "";
				if (r.get(13) != null && !"".equals(r.get(13))) {
					company = Integer.parseInt(r.get(13));
					if (company < 10) {
						companyStr = companyStr + "0" + company;
					} else {
						companyStr = companyStr + company;
					}
				}

				jewel.setNum(companyStr + r.get(1));//编号
				jewel.setShape(r.get(2));//形状
				jewel.setWeight(Float.parseFloat(r.get(3)));//重量
				jewel.setColor(r.get(4));//颜色
				jewel.setNeatness(r.get(5));//净度
				jewel.setCut(r.get(6));//切工
				jewel.setBuffing(r.get(7));//抛光
				jewel.setSymmetry(r.get(8)); //对称
				jewel.setFluorescence(r.get(9));//荧光
				jewel.setCard(r.get(10));//证书
				jewel.setSize(r.get(11));//尺寸
				jewel.setInternationalPrice(Integer.parseInt(r.get(12)));//国际报价
				jewel.setCompany(r.get(13));//公司
				jewel.setDiscount(Float.parseFloat(r.get(14)));//折扣
				//				jewel.setPriceRMB(Integer.parseInt(r.get(15))); //人民币/价格
				jewel.setHoldType(r.get(16));//库存 
				jewel.setCardId(r.get(17));//证书号
				jewel.setHasMilkColor(r.get(18));//有无奶油色
				jewel.setHasCoffeeColor(r.get(19));//有无咖啡色
				jewel.setHasGreenColor(r.get(20));//有无绿色
				jewel.setId(0L);
				if ("海外库存".equals(jewel.getHoldType())) {
					jewel.setHoldTypeSort(2);
				} else {
					jewel.setHoldTypeSort(1);
				}
				jewelDao.save(jewel);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return true;
	}

	public SXSSFWorkbook getEXL(List<Jewel> jewels, ExchangeRate exchangeRate) {
		try {
			//			int totalRow = 0;
			// 声明一个工作薄
			//			XSSFWorkbook workbook = new XSSFWorkbook();
			SXSSFWorkbook workbook = new SXSSFWorkbook(100);
			loadJewel(jewels, workbook, exchangeRate);
			return workbook;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	private void loadJewel(List<Jewel> jewels, SXSSFWorkbook workbook, ExchangeRate exchangeRate) {
		// 生成一个表格
		Sheet xssfSheet = workbook.createSheet("sheet1");
		Row bodyRow = xssfSheet.createRow(0);
		/**
		* 载入非二级零售商的钻石数据
		* @param jewels
		* @param workbook
		*/
		Cell cell;

		for (int i = 0; i < orderJewelTitle1.length; i++) {//处理标题
			cell = bodyRow.createCell(i);
			cell.setCellValue(orderJewelTitle1[i]);
		}
		for (int j = 0; j < jewels.size(); j++) {
			bodyRow = xssfSheet.createRow(j + 1);
			for (int i = 0; i < orderJewelTitle1.length; i++) {//处理标题
				cell = bodyRow.createCell(i);
				switch (i) {
				case 0:
					cell.setCellValue(jewels.get(j).getNum());//货号
					break;
				case 1:
					//						xssfSheet.setColumnWidth((short) 1, (short) (300 * 35));
					cell.setCellValue(jewels.get(j).getShape());//形状
					break;
				case 2:
					cell.setCellValue("" + jewels.get(j).getWeight());//重量
					break;
				case 3:
					cell.setCellValue(jewels.get(j).getColor());//颜色
					break;
				case 4:
					cell.setCellValue(jewels.get(j).getNeatness());//净度
					break;
				case 5:
					cell.setCellValue(jewels.get(j).getCut());//切工
					break;
				case 6:
					cell.setCellValue(jewels.get(j).getBuffing());//抛光	
					break;
				case 7:
					cell.setCellValue(jewels.get(j).getSymmetry());//对称
					break;
				case 8:
					cell.setCellValue(jewels.get(j).getFluorescence());//荧光
					break;
				case 9:
					cell.setCellValue(jewels.get(j).getCard());//证书
					break;
				case 10:
					cell.setCellValue(jewels.get(j).getSize());//尺寸
					break;
				case 11:
					cell.setCellValue(jewels.get(j).getInternationalPrice());//国际报价
					break;
				case 12:
					cell.setCellValue(jewels.get(j).getCompany());//公司
					break;
				case 13:
					//					DecimalFormat fnum1 = new DecimalFormat("##0.00");
					cell.setCellValue("" + jewels.get(j).getDiscount());//折扣
					break;
				case 14:
					float discount = (float) ((100.0 + jewels.get(j).getDiscount()) / 100.0);
					DecimalFormat fnum2 = new DecimalFormat("##0");
					cell.setCellValue(fnum2.format(jewels.get(j).getWeight() * jewels.get(j).getInternationalPrice()
							* (discount) * exchangeRate.getRate()));//人民币价格
					break;
				case 15:
					cell.setCellValue(jewels.get(j).getHoldType());//库存
					break;
				case 16:
					cell.setCellValue(jewels.get(j).getCardId());//证书号
					break;
				case 17:
					cell.setCellValue(jewels.get(j).getHasMilkColor());//有无奶色
					break;
				case 18:
					cell.setCellValue(jewels.get(j).getHasCoffeeColor());//有咖啡
					break;
				case 19:
					cell.setCellValue(jewels.get(j).getHasGreenColor());//有绿色
					break;
				default:
					break;
				}
			}
		}
	}

	@Override
	protected JewelDao getDao() {
		return jewelDao;
	}
}
